

/*DataSourse连接数据库*/
/*解决sql注入的问题*/
//以后生产中，都用DataSourse连接数据库


import com.mysql.cj.jdbc.MysqlDataSource;

import javax.sql.DataSource;
import java.sql.*;
import java.text.MessageFormat;
import java.util.Scanner;

public class Demo03 {
    public static void main(String[] args) {
        //定义MYSQL数据源对象
        MysqlDataSource mysqlDataSource = new MysqlDataSource();
        //设置数据库的连接串url
        mysqlDataSource.setURL("");
        //设置用户名
        mysqlDataSource.setUser("root");
        //设置密码
        mysqlDataSource.setPassword("123456");
        //定义JDBC数据源对象
        DataSource dataSource = mysqlDataSource;


        //定义对象，方便之后释放资源
        //声明数据库连接对象
        Connection connection = null;
        //声明SQL语句的预处理对象（可以过滤掉非法字符-》通过这个预处理对象来解决注入问题
        PreparedStatement statement = null;
        //声明结果集对象
        ResultSet resultSet = null;

        try {
            //1.获取数据库连接
            connection = dataSource.getConnection();

            //2.定义SQL语句
            String sql = "select id,name,sno,age,gender,enroll_date,class_id from student where name = ?";//动态传入的参数用？占位，在预处理时可以进行替换

            //接收用户输入的姓名
            Scanner scanner = new Scanner(System.in);
            System.out.println("请输入要查询的学生姓名：");
            String inputName = scanner.next();

            //3.获取SQL预处理对象
            statement = connection.prepareStatement(sql);
            //4.用真实的姓名去替换占位符
            statement.setString(1, inputName);

            //5.执行sql语句
            resultSet = statement.executeQuery();

            //6.获取结果集
            while (resultSet.next()) {
                long id = resultSet.getLong("id");//ctrl+p看参数列表   建议里面传Label
                String name = resultSet.getString("name");
                String sno = resultSet.getString("sno");
                int age = resultSet.getInt("age");
                boolean gender = resultSet.getBoolean("gender");
                Date enroll_date = resultSet.getDate("enroll_date");
                long class_id = resultSet.getLong("class_id");
                //打印结果
                System.out.println(MessageFormat.format("学生编号={0}，姓名={1}，学号={2}，年龄={3}，" +
                        "性别={4}，入学时间={5}，班级编号={6}", id, name, sno, age, gender, enroll_date, class_id));//使用占位符输出
            }
        } catch (SQLException e) {
            throw new RuntimeException(e);
        } finally {
            if (resultSet != null) {
                try {
                    resultSet.close();
                } catch (SQLException e) {
                    throw new RuntimeException(e);
                }
            }

            if (statement != null) {
                try {
                    statement.close();
                } catch (SQLException e) {
                    throw new RuntimeException(e);
                }
            }

            if (connection != null) {
                try {
                    connection.close();
                } catch (SQLException e) {
                    throw new RuntimeException(e);
                }
            }
        }
    }
}
